SharePoint Integration

Overview

Execute allows attaching supporting documentation (files) to most records within Execute (AFEs, RTx, etc.).

Most commonly, these files are managed and stored by Execute on a network file share. In some cases, however, it is beneficial, or perhaps required, to store these files in SharePoint. This document walks through configuring Execute to store attached files in SharePoint, rather than using the default network file share approach.

Caveats

  • When Execute is configured to store attachments in SharePoint, all attachments (for all document types) will be stored in SharePoint. This means it is not currently possible to store AFE attachments in SharePoint, while RTx attachments are stored on the network file share, for example.
  • This integration supports SharePoint online (through Office365). On-prem installations of SharePoint are not currently supported.
  • The folder structure of attachments is managed by Execute and is optimized for programmatic access and not direct user access. SharePoint views can be used to present attachments in a more user-friendly way, if required.
  • There is no migration of attachments from the traditional network file share-based approach to the new SharePoint approach. This means that enabling SharePoint integration should only be undertaken on new (empty) Execute databases. Enabling SharePoint integration on an existing Execute database will mean that all previous attachments will be lost.

Features

  • Seamless integration with SharePoint (the user experience is the same when attaching files using the traditional network file share-based approach, or with SharePoint)
  • Supports writing additional columns to SharePoint (to facilitate creating useful SharePoint views)
  • Attachment versioning is captured within SharePoint (but still visible within Execute)

Configuration

Configuration of Execute to store file attachments in SharePoint is broken into two parts:

  • Configuring the SharePoint Document Library
  • Configuring Execute

SharePoint Configuration

Creating a Document Library

It is strongly recommended that a new Document Library be created to store Execute file attachments. This avoids conflicts between Execute’s managed folder structure, and any pre-existing content. It also allows the definition of additional information columns, and tuning of configuration settings, without inadvertently impacting other SharePoint content.

When creating a new Document Library, we recommend that you:

  1. Set Versioning to Create major and minor (draft) versions.
  2. Set Require documents to be checked out before they can be edited to No.

Aside from the above, the default settings should be sufficient.

When creating a new document library, the following new SharePoint columns must be created to capture attachment meta-data (note: the Name must be as provided below).

Name Type Description
Uploaded Comment single-line Tracks the comment the user provided when uploading the attachment in Execute.
UploadedBy single-line Tracks the Execute user who uploaded the attachment. Because all attachments are uploaded by the Execute service, the out of the box “Modified By” field always reflects the service account.

Adding additional SharePoint Columns

A SharePoint administrator can create additional columns in SharePoint using the SharePoint website. Execute can then be configured to write information from the document (AFE, RTx, etc.) into these additional columns when new attachments are added, and upon every update to the document (AFE, RTx, etc.). This facilitates building user-friendly views on top of the attachments stored in SharePoint.

Execute currently only support single-line, multi-line, hyperlink, number and date/time fields.

Execute Configuration

To configure Execute to use SharePoint, you will need to have the following information:

Field Description Example
SharePointServerUrl Full URL (including http/https) to your SharePoint instance https://company.sharepoint.com/Execute
SharePointDocumentLibrary Name of the Document Library created to store Execute attachments Documents
SharePointRootFolder Root Folder name within the Document Library where all attachments are written to Execute
SharePointAccountName Username for SharePoint user with access to read/write/update the Document Library. Note: User can’t use 2FA. ExecuteService@company.com
SharePointAccountPassword Password for SharePoint user (above) 123
SharePointOnlineAuth Whether to use SharePoint online authentication (currently, only “true” is supported) true
SharePointColumns Additional SharePoint columns to write information into. Execute Support will help with the configuration of these additional columns.

SharePoint integration is enabled by editing config\attachments.config (in the service folder).

  1. Enable SharePoint integration by uncommenting the USE_SHAREPOINT line. i.e. Change: <!-- <?define USE_SHAREPOINT ?> --> to: <?define USE_SHAREPOINT ?>.
  2. Update the SharePoint configuration settings (as per the above).
  3. Restart the Execute service.

Adding Additional Columns

A mapping can be set up to fill additional SharePoint columns with information from Execute. These mappings are added to the “SharePointColumns” list within config\attachments.config.

Enabling SharePoint for attachment storage is a global setting affecting all attachments, regardless of the type of document they are attached to (AFE, RTx, etc.). This means that when it comes to configuring additional columns, it is often required to filter based on the type of document (i.e. Only fill in the AFE Cost Center SharePoint column if the attachment is being attached to an AFE).

When configuring Execute to write to a new SharePoint column, you will need:

  • The name of the pre-existing SharePoint column (case-insensitive)
  • An expression to extract the required information from the document in Execute (often Execute Support will be required to build this expression)

You’ll then add a new <item> to the <array> block, and restart the Execute Service.

<item>
<name>My New SharePoint Field</name>
<expression><![CDATA[
Expression Goes Here
]]></expression>
</item>

You can add any number of additional SharePoint columns in this way.

Sample Expressions:

Expression Description
doc.ReadTextField("DESCRIPTION") Read the value of the “DESCRIPTION” text field from the document. May fail if there is no “DESCRIPTION” text field.
doc.Record.FieldExists("DESCRIPTION")  
? doc.ReadTextField("DESCRIPTION")  
: "" A safer version of the above that only reads the value from the “DESCRIPTION” field, if that field exists.
doc.Type.Name == "RTX"  
? doc.ReadTextField("CUSTOM/LAHEE_CLASSIFICATION/LAHEE")  
: "" Read the LAHEE value from the LAHEE_CLASSIFICATION custom field. Only applies to RTX document types.
doc.Type.Name == "AFE"  
? doc.ReadTextField("AFENUMBER_DOC/AFENUMBER")  
: (doc.Type.Name == "RTX" ? doc.ReadTextField("RTX_NUMBER"): "")  
  Return the AFE Number for AFE documents, the RTX Number for RTx documents, and nothing for everything else.

FAQ

What happens if I delete a file in SharePoint?

We don’t typically don’t recommend managing attachment files (including deleting them) directly SharePoint. However, in some cases, it is necessary to delete a file manually within SharePoint, such as:

  • When a sensitive file was mistakenly attached and all record of that needs to be removed
  • When policy dictates that attachments should be removed (all records older than seven years, for example)

To improve performance, Execute caches information from SharePoint. This means that if you manually make changes to SharePoint, you may run into issues where Execute shows old information. For this reason, it is strongly recommended that the Execute Service be stopped when manually removing attachments from SharePoint (or restarted as soon as possible after making those changes).

Can I upload files directly to SharePoint?

It is possible, but not recommended. New attachments written to the Document’s folder (these folders are machine managed and complicated) will show up in Execute provided:

  • The Execute is restarted after adding the new attachment(s)
  • The required “UploadedBy” additional SharePoint column is filled in correctly

The primary use-case for this is the programmatic migration of attachments from a different attachment store into SharePoint.

Can I migrate from Network File Storage to SharePoint?

At this point, there is no automated migration of attachments from the Network File Share to SharePoint.

What happens if I delete the AFE/RTx in Execute?

Records in Execute are almost never actually deleted. In practice, records are soft-deleted (to allow for undelete and support audit history). Consequentially, supporting attachments are left as-is in SharePoint when deleting a document in Execute

What is with that folder structure I’m seeing in SharePoint?

The folder structure in SharePoint is designed to support efficient queries/updates from Execute, and is not intended to be accessed/viewed directly by the user. The path is composed of:

  • Root folder (defined in config\attachments.config)
  • First 2 characters from the document’s DOCUMENT_ID (or AFECHAINGUID for AFEs)
  • First 4 characters from the document’s DOCUMENT_ID (or AFECHAINGUID for AFEs)
  • Document’s full DOCUMENT_ID (or AFECHAINGUID for AFEs)

What if I need to migrate to a different SharePoint site?

As long as the folder structure (under the Root Path) is migrated to a Library on the new SharePoint site, and the configuration file (config\attachments.config) is updated accordingly, Execute will use the new site without issue.

What happens if I add a new SharePoint column, or change a column expression, after I have attachments?

Execute only writes to SharePoint columns when:

  • A new attachment is uploaded
  • The underlying document (AFE, RTx, etc.) is updated and the value for those SharePoint columns changes.

This means that, if you change the expression for an existing column, or add a new one, any pre-existing attachments will not be updated until the underlying document (AFE, RTx, etc.) is updated.

For this reason, it is preferable to ensure that all columns are added and configuration is tested before uploading any attachments.